/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author Thanh
 */
public class UserDAL {

    public static void insert(String username, String password, String gender, String role, String fullName, String tel, String address, String customerLevel, String nationality) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement psmt = null;
        String sql = "insert into Users(UserName, Password, Gender, Role, FullName,"
                + " Tel, Address, CustomerLevel, Nationality)" + "values(?,?,?,?,?,?,?,?,?)";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1, username);
            psmt.setString(2, password);
            psmt.setString(3, gender);
            psmt.setString(4, role);
            psmt.setString(5, fullName);
            psmt.setString(6, tel);
            psmt.setString(7, address);
            psmt.setString(8, customerLevel);
            psmt.setString(9, nationality);
            psmt.executeUpdate();
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
    }
    //checkLogin function to check whether the username and password are existed in database

    public static boolean checkLogin(String username, String password) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from Users " + "where UserName=? and Password=?";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1, username);
            psmt.setString(2, password);
            rs = psmt.executeQuery();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
    }

    public static UserEntity setLogin(String username) throws ClassNotFoundException, SQLException {
        Connection con = null;
        UserEntity entity = new UserEntity();
        PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from Users " + "where UserName=?";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1, username);
            rs = psmt.executeQuery();
            rs.next();
            entity.setUsername(rs.getString("UserName"));
            entity.setAddress(rs.getString("Address"));
            entity.setFullName(rs.getString("FullName"));
            entity.setPassword(rs.getString("Password"));
            entity.setCustomerLevel(rs.getString("CustomerLevel"));
            entity.setTel(rs.getString("Tel"));
            entity.setGender(rs.getString("Gender"));
            entity.setNationality(rs.getString("Nationality"));
            entity.setRole(rs.getString("Role"));
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return entity;
    }

    public static Collection<UserEntity> listByRole(String role) throws ClassNotFoundException, SQLException {
        Connection con = null;
        Collection<UserEntity> list = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from Users " + "where Role=?";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1, role);
            rs = psmt.executeQuery();
            while (rs.next()) {
                UserEntity entity = new UserEntity();
                entity.setUsername(rs.getString("UserName"));
                entity.setAddress(rs.getString("Address"));
                entity.setFullName(rs.getString("FullName"));
                entity.setPassword(rs.getString("Password"));
                entity.setCustomerLevel(rs.getString("CustomerLevel"));
                entity.setTel(rs.getString("Tel"));
                entity.setGender(rs.getString("Gender"));
                entity.setNationality(rs.getString("Nationality"));
                entity.setRole(rs.getString("Role"));
                list.add(entity);
            }
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
    
        public static Collection<UserEntity> listAll() throws ClassNotFoundException, SQLException {
        Connection con = null;
        Collection<UserEntity> list = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from Users ";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            rs = psmt.executeQuery();
            while (rs.next()) {
                UserEntity entity = new UserEntity();
                entity.setUsername(rs.getString("UserName"));
                entity.setAddress(rs.getString("Address"));
                entity.setFullName(rs.getString("FullName"));
                entity.setPassword(rs.getString("Password"));
                entity.setCustomerLevel(rs.getString("CustomerLevel"));
                entity.setTel(rs.getString("Tel"));
                entity.setGender(rs.getString("Gender"));
                entity.setNationality(rs.getString("Nationality"));
                entity.setRole(rs.getString("Role"));
                list.add(entity);
            }
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
}
